package com.aide.simplification.global.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.configuration.ConfigurationException;

import com.aide.simplification.global.GlobalConfig;

public class DatabaseOP extends DataBase {
	public DatabaseOP(String poolname) {
		String dbpool ="";
		String dbtype ="";
		boolean dbdebug =false;
		//ProxoolDataSource pds = new ProxoolDataSource();
		try {
			GlobalConfig gc = new GlobalConfig();
			dbpool = gc.getString(poolname+".db.pool");
			dbtype = gc.getString(poolname+".db.type");
			dbdebug = gc.getBoolean(poolname+".db.debug");
		} catch (ConfigurationException e) {
		}
		if(dbpool==null||dbpool.equals("")){
			dbpool="sorc";
		}
		if(dbtype==null||dbtype.equals("")){
			dbtype="mssql";
		}
		super.setDatabasetype(dbtype);
		super.setPoolname(poolname);
		super.setDebug(dbdebug);
	}

	public int executeUpdate(String sql) {
		return super.executeUpdate(sql, new Object[0]);
	}

	public List<Map<String, Object>> queryForList(String sql) {
		return queryForList(sql, new Object[0]);
	}
	public List<Map<String, Object>> queryForListPage(String sql,Object[] params,String indexKey,int page,int pagesize) {
		String sqltemp = this.initPageSql(page, pagesize, indexKey, sql);
		Pattern pattern = Pattern.compile("\\?");
	    Matcher matcher = pattern.matcher(sqltemp);
	    int num = 0;
	    for (boolean flag = matcher.find(); flag; flag = matcher.find()) {
	         num++;
	    }
	    Matcher matcherbase = pattern.matcher(sql);
	    int base = 0;
	    for (boolean flag = matcherbase.find(); flag; flag = matcherbase.find()) {
	    	base++;
	    }
	    Object[] objs=new Object[num];
	    for (int i = 0; i < objs.length; i++) {
	    	objs[i]=params[i%base];
		}
		return queryForList(sqltemp, objs);
	}
	public List<Map<String, Object>> queryForListPage(String sql,String indexKey,int page,int pagesize) {
		return queryForListPage(sql, new Object[]{}, indexKey, page, pagesize);
	}
	public Map<String, Object> queryForMap(String sql) {
		return queryForMap(sql, new Object[0]);
	}

	public int queryForInt(String sql) {
		return queryForInt(sql, new Object[0]);
	}

	public String queryForString(String sql) {
		return queryForString(sql, new Object[0]);
	}
	

	/**
	 * 按照主键查询
	 * 
	 * @param tablename
	 * @param pkname
	 *            主键
	 * @param pkvalue
	 *            值
	 * @return
	 */
	public Map<String, Object> findByPK(String tablename, String pkname,
			Object pkvalue) {
		String sql = "select * from " + tablename + " where "+pkname+"=?";
		return this.queryForMap(sql, new Object[] { pkvalue });
	}
	public String queryForString(String tablename,String colName, String pkname,
			Object pkvalue) {
		String sql = "select "+colName+" from " + tablename + " where pkname=?";
		return this.queryForString(sql,new Object[]{pkvalue});
	}
	/**
	 * 按主键查询（适用联合主键表）
	 * 
	 * @param tablename
	 * @param map
	 * @return
	 */
	public Map<String, Object> findByPK(String tablename,
			Map<String, Object> map) {
		String sql = "select * from " + tablename + " where ";
		List<Object> params = new ArrayList<Object>();
		for (Iterator<String> iterator = map.keySet().iterator(); iterator
				.hasNext();) {
			String key = iterator.next();
			if(iterator.hasNext()){
				sql += (key + "=? and ");
			}else{
				sql += (key + "=?");
			}
			params.add(map.get(key));
		}
		return this.queryForMap(sql, params.toArray());
	}

	/**
	 * 批量添加数据到表
	 * 
	 * @param tablename表名
	 * @param list
	 *            list里存放多个 要存储数据的哈希表 字段名为key字段值为value
	 * @param keyColName
	 *            主键字段名 如果非空自动使用UUID生成值
	 * @return 添加数据成功数
	 */
	public int save(String tablename, List<Map<String, Object>> list) {
		int count = 0;
		for (Iterator<Map<String, Object>> it = list.iterator(); it.hasNext();) {
			Map<String, Object> hm = it.next();
			if (this.save(tablename, hm) > 0) {
				count++;
			}
		}
		return count;
	}

	/**
	 * 添加数据到表 如果keyColName为“”则不自动生成主键
	 * 
	 * @param tablename
	 *            表明
	 * @param hs
	 *            要存储数据的哈希表 字段名为key字段值为value
	 * @param keyColName
	 *            主键字段名 如果非空自动使用UUID生成值
	 * @return 是否成功添加数据
	 */
	public int save(String tablename, Map<String, Object> hm) {
		StringBuffer sql = new StringBuffer();
		StringBuffer sql2 = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sql.append("insert into " + tablename + " (");
		for (Iterator<String> iterator = hm.keySet().iterator(); iterator
				.hasNext();) {
			String colName = iterator.next();
			Object colVal = hm.get(colName);
			if (iterator.hasNext()) {
				sql.append(colName + ",");
				sql2.append("?,");
			} else {
				sql.append(colName + ") values(");
				sql2.append("?)");
			}
			params.add(colVal);
		}
		return this.executeUpdate(sql.toString() + sql2.toString(), params
				.toArray());
	}

	/**
	 * 按照指定条件更新表 条件只需写where后的语句
	 * 
	 * @param tablename
	 *            表名
	 * @param hm
	 *            更新数据
	 * @param tj
	 *            更新条件
	 * @return
	 */
	public int update(String tablename, Map<String, Object> hm,
			Map<String, Object> tj) {
		StringBuffer sql = new StringBuffer();
		sql.append("update " + tablename + " set ");
		List<Object> params = new ArrayList<Object>();
		for (Iterator<String> iterator = hm.keySet().iterator(); iterator
				.hasNext();) {
			String colName = iterator.next();
			Object colVal = hm.get(colName);
			if (iterator.hasNext()) {
				sql.append(colName + "=?,");
			} else {
				sql.append(colName + "=? where ");
			}
			params.add(colVal);
		}
		for (Iterator<String> iterator = tj.keySet().iterator(); iterator
				.hasNext();) {
			String colName = iterator.next();
			String colVal = tj.get(colName).toString();
			if (iterator.hasNext()) {
				sql.append(colName + "=? and ");
			} else {
				sql.append(colName + "=?");
			}
			params.add(colVal);
		}
		return this.executeUpdate(sql.toString(), params.toArray());
	}

	/**
	 * 按照主键更新表数据
	 * 
	 * @param tablename
	 * @param hm
	 * @param pkname
	 * @param pkvalue
	 * @return
	 */
	public int update(String tablename, Map<String, Object> hm, String pkname,
			String pkvalue) {
		Map<String, Object> tj = new HashMap<String, Object>();
		tj.put(pkname, pkvalue);
		return this.update(tablename, hm, tj);
	}

	/**
	 * 
	 * @param tablename
	 *            要删除记录所在表名
	 * @param hs
	 *            删除条件 列名和值对应的哈希表
	 * @return 返回是否删除成功
	 */
	public int delete(String tablename, Map<String, Object> hm) {
		if (tablename != null && tablename.length() > 0) {
			StringBuffer sql = new StringBuffer();
			List<Object> params = new ArrayList<Object>();
			sql.append("delete from " + tablename);
			if (!hm.isEmpty()) {
				sql.append(" where ");
				for (Iterator<String> iterator = hm.keySet().iterator(); iterator
						.hasNext();) {
					String colName = iterator.next();
					String colVal = hm.get(colName).toString();
					if (iterator.hasNext()) {
						sql.append(colName + "=? and ");
					} else {
						sql.append(colName + "=?");
					}
					params.add(colVal);
				}
			}
			return this.executeUpdate(sql.toString(), params.toArray());
		} else {
			System.out.println("tablename不能为空！");
			return -1;
		}
	}

	/**
	 * 按主键删除
	 * 
	 * @param tablename
	 *            表名
	 * @param keyColName
	 *            主键字段名
	 * @param value
	 *            主键对应值
	 * @return 是否删除成功
	 */
	public int delete(String tablename, String keyColName, Object value) {
		Map<String, Object> tj = new HashMap<String, Object>();
		tj.put(keyColName, value);
		return this.delete(tablename, tj);
	}

	/**
	 * 批量添加
	 * @param tablename
	 * @param vec
	 * @return
	 */
	public int saves(String tablename,Vector<Hashtable<String, Object>> vec) {
		StringBuffer sql = new StringBuffer();
		StringBuffer sql2 = new StringBuffer();
		sql.append("insert into " + tablename + " (");
		if (vec != null && vec.size() > 0) {
			Object[][] objs = new Object[vec.size()][vec.get(0).keySet().size()];
			for (Iterator<String> iterator = vec.get(0).keySet().iterator(); iterator
					.hasNext();) {
				String colName = iterator.next();
				if (iterator.hasNext()) {
					sql.append(colName + ",");
					sql2.append("?,");
				} else {
					sql.append(colName + ") values(");
					sql2.append("?)");
				}
			}
			for (int i = 0; i < vec.size(); i++) {
				Hashtable<String, Object> ht = vec.get(i);
				Set<String> set = ht.keySet();
				Iterator<String> iterator = set.iterator();
				for (int j = 0; j < set.size(); j++) {
					objs[i][j] = ht.get(iterator.next());
				}
			}
			return this.executeUpdateJT(sql.toString()+sql2.toString(), objs);
		}
		return -1;

	}
	public static void main(String[] args) {
	}
}